﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;

public partial class Pages_ColumnMapping : DatabasePage
{
    int ColumnMappingId
    {
        get
        {
            if (Request.QueryString["ColumnMappingId"] != null)
            {
                return Convert.ToInt32(Request.QueryString["ColumnMappingId"]);
            }
            else
            {
                return 0;
            }
        }
    }


    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            LoadTables();
            LoadReferenceDlls();
            SetContentHeader();

            if (ColumnMappingId != 0)
            {
                DBCDS.ColumnMappingRow cmRow = new DBCDSTableAdapters.ColumnMappingTableAdapter().GetDataById(ColumnMappingId)[0];
                ddlTable.SelectedValue = cmRow.TableName;
                LoadColumns(ddlColumn, ddlTable.SelectedValue);
                ddlColumn.SelectedValue = cmRow.TableColumnName;

                ddlReferenceObject.SelectedValue = cmRow.ReferenceObject;
                LoadReferenceDlls();
                ddlReferenceIdColumn.SelectedValue = cmRow.ReferenceIdColumn;
                ddlReferenceDisplayColumn.SelectedValue = cmRow.ReferenceDisplayColumn;
                ddlOrderBy.SelectedValue = cmRow.ReferenceOrderByColumn;
                ddlDirection.SelectedValue = cmRow.ReferenceOrderDirection;
            }
        }
    }

    private void SetContentHeader()
    {
        OutlookMaster om = (OutlookMaster)Master;
        om.ContentHeader.Text = "Column mapping";
    }

    private void LoadTables()
    {
        //Table
        ddlTable.DataSource = DBData.DatabaseObjects(ConnectionString, DatabaseObjectTypes.Tables);
        ddlTable.DataTextField = "table_name";
        ddlTable.DataValueField = "table_name";
        ddlTable.DataBind();

        LoadColumns(ddlColumn, ddlTable.SelectedValue);

    }

    private void LoadReferences()
    {
        //Reference
        if (ddlReferenceType.SelectedValue == "All")
        {
            ddlReferenceObject.DataSource = DBData.DatabaseObjects(ConnectionString, DatabaseObjectTypes.All);
        }
        else if (ddlReferenceType.SelectedValue == "Tables")
        {
            ddlReferenceObject.DataSource = DBData.DatabaseObjects(ConnectionString, DatabaseObjectTypes.Tables);
        }
        else if (ddlReferenceType.SelectedValue == "Views")
        {
            ddlReferenceObject.DataSource = DBData.DatabaseObjects(ConnectionString, DatabaseObjectTypes.Views);
        }

        ddlReferenceObject.DataTextField = "table_name";
        ddlReferenceObject.DataValueField = "table_name";
        ddlReferenceObject.DataBind();

        LoadColumns(ddlColumn, ddlTable.SelectedValue);
    }

    private void LoadColumns(DropDownList ddl, string tableName)
    {
        try
        {
            ddl.DataSource = GetColumns(tableName);
            ddl.DataTextField = "ColumnName";
            ddl.DataValueField = "ColumnName";
            ddl.DataBind();
        }
        catch (Exception ex)
        {
            //lblMessage.Text = ex.Message;
        }
    }

    public DataColumnCollection GetColumns(string tableName)
    {
        string selectStr = string.Format("SELECT TOP 1 * FROM [{0}] ", tableName);
        DataTable dt = DataAccess.GetDataTableSchema(ConnectionString, selectStr);
        return dt.Columns;
    }

    protected void ddlTable_SelectedIndexChanged(object sender, EventArgs e)
    {
        LoadColumns(ddlColumn, ddlTable.SelectedValue);
    }
    protected void ddlReferenceObject_SelectedIndexChanged(object sender, EventArgs e)
    {
        LoadColumns(ddlReferenceIdColumn, ddlReferenceObject.SelectedValue);
        LoadColumns(ddlReferenceDisplayColumn, ddlReferenceObject.SelectedValue);
        LoadColumns(ddlOrderBy, ddlReferenceObject.SelectedValue);
    }
    protected void ddlReferenceType_SelectedIndexChanged(object sender, EventArgs e)
    {
        LoadReferenceDlls();
    }

    private void LoadReferenceDlls()
    {
        LoadReferences();
        LoadColumns(ddlReferenceIdColumn, ddlReferenceObject.SelectedValue);
        LoadColumns(ddlReferenceDisplayColumn, ddlReferenceObject.SelectedValue);
        LoadColumns(ddlOrderBy, ddlReferenceObject.SelectedValue);
    }
    protected void btnSave_Click(object sender, EventArgs e)
    {
        if (ColumnMappingId == 0)
        {
            new DBCDSTableAdapters.ColumnMappingTableAdapter().Insert(
                ConnectionId,
                ddlTable.Text,
                ddlColumn.Text,
                ddlReferenceObject.Text,
                ddlReferenceIdColumn.Text,
                ddlReferenceDisplayColumn.Text,
                ddlOrderBy.Text,
                ddlDirection.Text);
        }
        else
        {
            new DBCDSTableAdapters.ColumnMappingTableAdapter().Update(
               ConnectionId,
               ddlTable.Text,
               ddlColumn.Text,
               ddlReferenceObject.Text,
               ddlReferenceIdColumn.Text,
               ddlReferenceDisplayColumn.Text,
               ddlOrderBy.Text,
               ddlDirection.Text,
               ColumnMappingId);
        }
        Response.Redirect("ColumnMappingList.aspx");
    }
    protected void btnDelete_Click(object sender, EventArgs e)
    {
        new DBCDSTableAdapters.ColumnMappingTableAdapter().Delete(ColumnMappingId);
        Response.Redirect("ColumnMappingList.aspx");
    }
}